<?php
	/**
	 * Modelo de ficheiro Excel para exportação de Projectos
	 * Precedencias:
	 * 		=> DIR_PROJECTOS/exportar_excel_projectos.php
	 * 
	 * @since 02/04/2008
	 * @author JRS
	 */
	
	#Includes SoftsetSense
	require_once ('../includes/config.php');
	include DIR_INCLUDES_FT.'bootstrap.php';
	require_once (DIR_TOOLKIT_FT.'phpExcel/EXCELExport.class.php');
	
	#Includes PHPExcel
	require_once (DIR_TOOLKIT_FT.'phpExcel/PHPExcel.php');
	require_once (DIR_TOOLKIT_FT.'phpExcel/PHPExcel/RichText.php');

class ExportarExcelContagem extends EXCELExport {
	
	/**
	 * Método Constructor
	 *
	 * @param unknown_type $projectoID
	 */
	public function __construct($contagem_id) {
		$this->contagem_id = $contagem_id;
		parent::__construct($this->geraExcel(), $this->getNomeFicheiro());
	}
	
	/*************************************************************************
	 ************************* INICIO DO DOCUMENTO ***************************/
	public function geraExcel () {
		
		ini_set("memory_limit","256M");
		
		$estiloNormal = array(
			'font' => array(
				'size' => 10,
				'bold' => true
			),
			'borders' => array(
				'bottom' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
				),
				'top' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
				),
				'left' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
				),
				'right' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
				)
			)
		);
		
		$estiloTitulo = array(
			'font' => array(
				'size' => 13,
				'bold' => true
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
			),
			'borders' => array(
				'bottom' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'top' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'left' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'right' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				)
			),
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	  			'rotation'   => 90,
	 			'startcolor' => array(
	 				'argb' => 'FFBFBFBF'
	 			),
	 			'endcolor'   => array(
	 				'argb' => 'FFBFBFBF'
	 			)
	 		)
		);
		
		$estiloCategorias = array(
			'font' => array(
				'size' => 10,
				'bold' => true
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
			),
			'borders' => array(
				'bottom' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'top' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'left' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'right' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				)
			),
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	  			'rotation'   => 90,
	 			'startcolor' => array(
	 				'argb' => 'FFFFFF00'
	 			),
	 			'endcolor'   => array(
	 				'argb' => 'FFFFFF00'
	 			)
	 		)
		);
		
		$estiloDadosCliente = array(
			'font' => array(
				'size' => 10,
				'bold' => true
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
			),
			'borders' => array(
				'bottom' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'top' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'left' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				),
				'right' => array(
					'style' => PHPExcel_Style_Border::BORDER_MEDIUM
				)
			),
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	  			'rotation'   => 90,
	 			'startcolor' => array(
	 				'argb' => 'FFD99795'
	 			),
	 			'endcolor'   => array(
	 				'argb' => 'FFD99795'
	 			)
	 		)
		);
		
		#Cria um novo objecto PHPExcel
		$objPHPExcel = new PHPExcel();
		#Define as propriedades do documento
		$objPHPExcel->getProperties()->setCreator("BiostrumentStocks");
		$objPHPExcel->getProperties()->setLastModifiedBy("BiostrumentStocks");
		$objPHPExcel->getProperties()->setTitle("Contagem");
		$objPHPExcel->getProperties()->setSubject("Contagem");
		$objPHPExcel->getProperties()->setDescription("Contagem");
		$objPHPExcel->getProperties()->setKeywords("Contagem");
		$objPHPExcel->getProperties()->setCategory("Contagem");
		
		#Remove as gridlines
		$objPHPExcel->getActiveSheet()->setShowGridlines(true);
		#Atribui um Header e um Footer para a impressão
		$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BContagem de Produtos&RImpresso em &D');
		$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPágina &P de &N');
		#Define o tamanho e orientação da página
		$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
		$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
		
		#Cria uma folha de dados
		$objPHPExcel->setActiveSheetIndex(0);
		#Modifica o nome da folha de dados
		$objPHPExcel->getActiveSheet()->setTitle('Contagem');
		#Retorna o objecto PHPExcel
		
		$contagem = Doctrine_Query::create()->from("Contagens")->where("id = ?", array($this->contagem_id))->execute(array(), Doctrine::HYDRATE_ARRAY);
		$contagem = $contagem[0];
		
		$loja = Doctrine_Query::create()->from("Lojas l")->innerJoin("l.Clientes c")->where("l.id = ?", array($contagem['loja_id']))->execute(array(), Doctrine::HYDRATE_ARRAY);
		$loja = $loja[0];
		
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Cliente: ");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 1)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, $loja['Clientes']['nome']);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, 1)->applyFromArray($estiloDadosCliente);
		
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, "Loja: ");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 2)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, $loja['nome']);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, 2)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 3, "Morada: ");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 3)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 3, $loja['morada']);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, 3)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 4, "Pessoa Responsável: ");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 4)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 4, "");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, 4)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 5, "Data inicio: ");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 5)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 6, "Data Fim: ");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0, 6)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 5, $contagem['data_inicio']);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1, 5)->applyFromArray($estiloDadosCliente);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 6, $contagem['data_fim']);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,6)->applyFromArray($estiloDadosCliente);
		
		$categorias = Doctrine_Query::create()->select("c.*, p.*, cp.*")->from("Categorias c")
						->innerJoin("c.Produtos p")
						->innerJoin("p.CodigosBarrasProdutos cp")
						->innerJoin("p.ProdutosLojas pl")
                        ->innerJoin("pl.Lojas l")->innerJoin("l.Contagens cont")
						->where("cont.id = ?", array($this->contagem_id))
						->execute(array(), Doctrine::HYDRATE_ARRAY);
		$linha = 8;
		$total_contagem = 0;
		foreach ($categorias as $categoria) {
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha)->applyFromArray($estiloTitulo);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $linha, $categoria['nome']);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha)->applyFromArray($estiloTitulo);
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $linha, 'Quantidade');
			$linha++;
			$categoria_total = 0; 
			foreach($categoria['Produtos'] as $produto) {
				$total = 0;
                foreach ($produto['CodigosBarrasProdutos'] as $codigo_barras){
                    $sub_total = Doctrine_Query::create()->select("ROUND(SUM(quantidade), 2) as total")
                        ->from('ContagensProdutos cp')
                        ->where("cp.contagem_id = ? and cp.codigo_barras_produto_id = ?", array($this->contagem_id,$codigo_barras['codigo_barras_id']))
                        ->execute(array(), Doctrine::HYDRATE_ARRAY);
                    $total += $sub_total[0]['total'];
                }
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $linha, $produto['nome']);
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $linha, $total);
				$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha)->applyFromArray($estiloNormal);
				$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha)->applyFromArray($estiloNormal);
				$linha++;
				$categoria_total +=$total;
			}
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $linha, "Total: ");
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha)->applyFromArray($estiloCategorias);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha)->getFill()->getStartColor()->setARGB("#FFFFFFFF");
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha)->getFill()->getEndColor()->setARGB("#FFFFFFFF");
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $linha, $categoria_total);
			$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha)->applyFromArray($estiloCategorias);
			$linha++;
			$total_contagem += $categoria_total;
		}
		
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $linha+2, "Número total itens contados");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha+2)->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha+2)->getFont()->setSize("13");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha+2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha+2)->getFill()->getStartColor()->setARGB("#FFBFBFBF");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(0,$linha+2)->getFill()->getEndColor()->setARGB("#FFBFBFBF");
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $linha+2, $total_contagem);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha+2)->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha+2)->getFont()->setSize("10");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha+2)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha+2)->getFill()->getStartColor()->setARGB("#FFFFFF00");
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow(1,$linha+2)->getFill()->getEndColor()->setARGB("#FFFFFF00");
		return $objPHPExcel;
	}

	/**
	 * Método herdado da Interface iExportExcel
	 *
	 */
	public function getNomeFicheiro () {
		$contagem = Doctrine_Query::create()->from("Contagens c")
					->innerJoin("c.Lojas l")
					->where("c.id = ?", array($this->contagem_id))->execute(array(), Doctrine::HYDRATE_ARRAY);
		$nome = str_replace(" ", "", $contagem[0]['Lojas']['nome']);
		$morada = str_replace(" ", "", $contagem[0]['Lojas']['morada']);
		return "contagem_".$nome."_".$morada."_".$contagem[0]['Lojas']['id']."_".date("[dmY_His]").".xls";
	}
}
?>